package DB;

import Model.Customer;
import java.sql.*;
import java.util.ArrayList;

public class CustomerDB implements CustomerDBIF{
    private Connection conn;

    public CustomerDB() {
        conn = ConnectionDB.getInstance().getConn();
    }

    @Override
    public int insertCustomer(Customer customer) {
        int rc = -1;

        String query="INSERT INTO customer(id, name, address, zipCode, phoneNo)  VALUES("+
                customer.getId()  + ",'"  +
                customer.getName()  + "','"  +
                customer.getAddress()  + "',"  +
                customer.getZipCode()  + ","  +
                customer.getPhoneNo()+")";


        System.out.println("insert : " + query);
        try{
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(SQLException ex){
            System.out.println("Customer Insert Error" + ex.getMessage());
        }
        return(rc);
    }

    @Override
    public Customer findCustomer(int id) {
        String wClause = "  id = " + id + "";
        return singleWhere(wClause);
    }

    public Customer searchCustomer(String attValue)
    {
        String wClause = "name like '%" + attValue + "%'";
        System.out.println("Search Customer " + wClause);
        return singleWhere(wClause);
    }

    public ArrayList<Customer> getAllCustomers(boolean retrieveAssociation) {
        return miscWhere("", false);
    }

    @Override
    public int updateCustomer(Customer customer) {
        int rc=-1;

        String query="UPDATE customer SET "+
                "id ="+ customer.getId()+", "+
                "name ='"+ customer.getAddress() + "', '" +
                "address ='"+ customer.getZipCode() + "', " +
                "zipCode = "+ customer.getPhoneNo() +
                " WHERE id = "+ customer.getId();

        System.out.println("Update query:" + query);
        try{
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);

            stmt.close();
        }
        catch(SQLException ex){
            System.out.println("Update exception in Customer db: "+ex);
        }
        return(rc);
    }

    @Override
    public int deleteCustomer(int id) {
        int rc=-1;

        String query="DELETE FROM customer WHERE id = " +
                id;
        System.out.println(query);
        try{
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception ex){
            System.out.println("Delete exception in customer db: "+ex);
        }
        return(rc);
    }

    public String getZipCodeCity(int zipCode){
        String result="";
        ResultSet results;
        String query="SELECT * FROM zipCodeCity WHERE zipCode = "+zipCode;
        try{

            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if( results.next() ){
                //customer = buildCustomer(results);
                result =  results.getString("city");
                stmt.close();
            }

        }
        catch (SQLException e){
            System.out.println("Query exception - select: "+e);
        }
        return result;
    }

    private Customer buildCustomer(ResultSet results){
        Customer customer = new Customer();
        try{
            customer.setId(results.getInt("id"));
            customer.setName(results.getString("name"));
            customer.setAddress(results.getString("address"));
            customer.setZipCode(results.getInt("zipCode"));
            customer.setCity(getZipCodeCity(results.getInt("zipCode")));
            customer.setPhoneNo(results.getInt("phoneNo"));
        }catch (SQLException e){
            System.out.println("Query exception - select: "+e);
        }

        return customer;
    }



    private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation){
        ResultSet results;
        ArrayList<Customer> list = new ArrayList<Customer>();
        String query = buildQuery(wClause);
        System.out.println(query);

        try{ // read the employee from the database
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while( results.next() ){
                Customer customer = new Customer();
                customer = buildCustomer(results);
                list.add(customer);
            }//end while
            stmt.close();
        }
        catch(Exception e){
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
        return  list;
    }

    private Customer singleWhere(String wClause)
    {
        ResultSet results;
        Customer customer = new Customer();

        String query =  buildQuery(wClause);
        System.out.println(query);
        try{
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if( results.next() ){
                customer = buildCustomer(results);
                stmt.close();
            }
        }
        catch(Exception e){
            System.out.println("Query exception: "+e);
        }
        return customer;
    }

    private String buildQuery(String wClause)
    {
        String query="SELECT id, name, address, zipCode, phoneNo FROM customer";

        if (wClause.length()>0)
            query=query+" WHERE "+ wClause;

        return query;
    }

    public int customerGetMax() {
        int nextId = GetMax.getMaxId("SELECT max(id) from Customer");

        nextId = nextId +1;

        return nextId;
    }
}
